SQL: Commonly Used Statements
What is SQL? ---- SQL, an abbreviation of Structured Query Language, (pronounced see-kwell or as separate letters) is a standardized query language for requesting information from a database (typically relational). The initial version, known as SEQUEL (an abbreviation of Structured English Query Language), was designed by an IBM research center during 1974 and 1975 for use with its mainframes. SQL was first introduced as a commercial database system in 1979 by the Oracle Corporation in support of DBMS (Database Management Systems) client/server environments. In 1986, ANSI approved a rudimentary version of SQL as the official standard, but most versions of SQL since then have included many extensions to the ANSI standard. Again, in 1991, ANSI updated the standard which is currently known as SAG SQL. Historically, SQL has been the primary query language for DBMS operation on minicomputers and mainframes. More and more SQL is being supported by PC database systems due to its support abilities on distributed databases (databases spread over multiple computer systems) enabling multiple users to access a single database on a LAN (Local Area Network) simultaneously. Today SQL is taught among educational campuses nationwide and can be seen in use among current industry-leading companies (such as Excelsior Software, Inc. headquartered in Greeley, Colorado). With the knowledge of proper database management and the implementation of SQL the growth potential of the relational database model is ever-changing. (Source: http://isp.webopedia.com/TERM/S/SQL.html ) Commonly Used SQL Commands: ---- Below are the most commonly used SQL statements in the IT job environment today: •SELECT •DELETE FROM •TRUNCATE TABLE •UPDATE SQL Command Implementation: ---- SELECT - The SELECT statement is the most commonly used statement to query a database for information contents. 'SELECT column_name(s) FROM table_name(s)' -- This statement will get the information of a particular column (attribute) from a specified table. 'SELECT * FROM table_name(s)' -- The * in this statement means that all columns (attributes) of the table will be returned on the query as gathered from a determined table (rather than just a single attributes). 'SELECT DISTINCT column_name(s) FROM table_name(s)' -- The DISTINCT label in this statement means that a single value of the attribute will be returned upon a query. (A great statement to execute if you would like to see a school name display once in your query rather than the twenty-five times it is recorded in your database. ---- DELETE FROM – The DELETED statement is most commonly used to delete entire tables or data held within tables. 'DELETE FROM table_name (NOTE: Deletes the entire table!!!) or DELETE FROM table_name WHERE condition' -- This statement deletes either an entire table or, if specified by the WHERE conditional clause, deletes the records (also referred to as rows or tuples) where a condition is met. ---- TRUNCATE TABLE (Only deletes the data inside of the table.) - The TRUNCATE TABLE statement is most commonly used to delete the information contained within a table. 'TRUNCATE TABLE table_name' -- This statement will delete/clear the information contained within a table. ---- UPDATE - The UPDATE statement is most commonly used to update a particular cell within a table with new/updated data. 'UPDATE table_name SET column_name =new_value WHERE column_name=some_value' -- This statement will update the data value in a given cell for a particular table given the existence of a current data value in the cell. (Source: http://www.w3schools.com ) -Complex Select Statements Select statements are capable of doing more than just showing some data from a specified table. They are able to produce Joins, Aggregations, and other complex functions beyond selecting data. This is accomplished by using additions to the select statement. The full format for a select statement is: SELECT {field list | * | DISTINCT ROW field} FROM table-list WHERE expression GROUP BY group-fields HAVING group expression ORDER BY field list Each of these lines allow the developer to manipulate the data in different ways through selecting different attributes to sort or search by. You can perform multi table joins by using the multiple tables in the select statement. This looks like the following: Multi-table Joins (using standard select and sub-select), self-joins SELECT {field list | * | DISTINCT ROW field} FROM table1, table2, … WHERE table1.col = table2.col GROUP BY group-fields HAVING group expression ORDER BY field list This allows the creation of reports and tables consisting of data from multiple tables In addition to these DML or Data Manipulation Language commands, there are several commonly used DCL or Data Control Language commands. These are Commit and Rollback. Commit Saves any changes to the database permanently and is used to protect data that has been input into the system from system failures. These commit statements are usually strategically placed to allow for users to preserve the input data while allowing for retrieval of previous states of the system through rollback statements. Once a Commit statement has been run it is permanent and cannot be rolled back. The Syntax for a Commit is: COMMIT; Rollbacks allow the system to be recovered back to the previous commit if a failure or miss-manipulation of the database is to occur. Rollbacks literally set the system back to the previous commit. The Syntax for a Rollback is: ROLLBACK;